﻿Imports FastReport.Report
Public Class frmRptDupCID
    Dim Strsql As String
    Dim _mysql As New ClsSQLhelper
    Public Report_Name As String
    Public Primary_Key As Integer
    Public CID_Criteria As String
    Private Sub frmRptDupCID_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Try
            Strsql = "Select CID from m_person where CODE_ID like '%PE9202%'"
            Dim DT_m_person_Dup As New DataTable
            DT_m_person_Dup = _mysql.GetMYSQLDataTable(Strsql, "m_person")
            If DT_m_person_Dup.Rows.Count = 0 Then
                MsgBox("ไม่พบข้อมูลซ้ำซ้อน", MsgBoxStyle.Information)
                Exit Sub
            End If
            For i = 1 To DT_m_person_Dup.Rows.Count - 1
                CID_Criteria = CID_Criteria & "'" & DT_m_person_Dup.Rows(i).Item("CID").ToString & "',"
            Next


            If CID_Criteria <> Nothing Then
                CID_Criteria = Mid(CID_Criteria, 1, Len(CID_Criteria) - 1) & ")"

            End If


            Strsql = "select distinct p.cid,p.pname,p.fname,p.lname, p.hn,CONCAT(p2.pname,p2.fname,' ',p2.lname) as name2,p.sex," & _
" p.birthday,p.last_visit,p.fathername,p.mathername,p.hometel,if(p.nationality = ' ','00',p.nationality) as nationality, " & _
" p2.pname as pname2,p2.fname as fname2,p2.sex as Sex2,p2.lname as lanme2, p2.hn as hn2, p2.birthday as BD2,p2.last_visit as lastvisit2,p2.fathername as fathername2,p2.mathername as mothername2,p2.hometel as hometel2,if(p2.nationality = ' ','00',p2.nationality) as nationality2, " & _
" (select name from nationality where nationality = p.nationality) as nationname, " & _
" (select name from nationality where nationality = p2.nationality) as nationname2, " & _
" if(p.birthday=p2.birthday,1,0) as birthdatematch, " & _
" if(p.tmbpart=p2.tmbpart,1,0) as Tmbmatch, " & _
" if(p.amppart=p2.amppart,1,0) as Ampmatch, " & _
" if(p.chwpart=p2.chwpart,1,0) as Chwmatch, " & _
" if(p.sex=p2.sex,1,0) as Sexmatch, " & _
" if(p.lname=p2.lname,1,0) as lnamematch, " & _
" if(p.fname=p2.fname,1,0) as fnamematch, " & _
" if(p.nationality=p2.nationality,1,0) as nationalitymatch " & _
" from patient p, patient p2 " & _
"             where(p.cid = p2.cid) " & _
" and   p.hn <> p2.hn " & _
" and p.CID in (" & CID_Criteria & _
" and length(p.cid)=13 " & _
" group by cid order by p.cid,fnamematch,lnamematch,birthdatematch,Sexmatch,Tmbmatch,Ampmatch,Chwmatch,p.nationality, p.hn "
            Dim DS_Documents As New DataSet
            DS_Documents = _mysql.GetMYSQLDataset(Strsql, "IDM", "reporttemplate", True)
            Dim TotalRow As Integer = DS_Documents.Tables(0).Rows.Count
            Dim AddRow As Integer
           

            Dim report1 As New FastReport.Report

            report1.Preview = PreviewControl2
            report1.Load(Application.StartupPath & "/Reports/rptDupCID.frx")


            report1.AutoFillDataSet = True

            report1.RegisterData(DS_Documents)

            report1.GetDataSource("reporttemplate").Enabled = True

            report1.Refresh()
            report1.Preview = PreviewControl2
            
            report1.Show()




        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub


End Class